package com.maxd.respository.userinfo;

import com.maxd.model.UserLog;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.Map;

/**
 * @description 用户登录日志
 * @author fang
 * @date 2021-03-23
 */
@Repository
public interface UserLogRepository extends JpaRepository<UserLog, Long> {


    @Query(value = "select * from user_log where user_id=:userId and date_format(update_time,'%Y-%m-%d')=date_format(:time,'%Y-%m-%d') order by update_time desc limit 1",nativeQuery = true)
    UserLog selectUserLog(@Param("userId") Long userId,@Param("time") String time);

    @Query(value = "select count(*) from user_log where if(:flag!=1,if(:flag!=2,date_format(update_time,'%Y-%m-%d')=date_format(:time,'%Y-%m-%d'),date_format(update_time,'%Y-%m')=date_format(:time,'%Y-%m')),date_format(update_time,'%Y')=date_format(:time,'%Y'))",nativeQuery = true)
    int selectUserLogCountByTime(@Param("flag") Integer flag,@Param("time") String time);

    @Query(value = "select count(*) from user_log where if(:flag!=1,if(:flag!=2,date_format(update_time,'%Y-%m-%d')=date_format(:time,'%Y-%m-%d'),date_format(update_time,'%Y-%m')=date_format(:time,'%Y-%m')),date_format(update_time,'%Y')=date_format(:time,'%Y')) " +
            " and user_id in (select id from user_info where grade_index=:gradeIndex)",nativeQuery = true)
    int selectUserLogCountByTimeAndGradeIndex(@Param("flag") Integer flag,@Param("time") String time,@Param("gradeIndex") Integer gradeIndex);

    @Query(value = "select count(*) from user_log where if(:flag!=1,if(:flag!=2,date_format(update_time,'%Y-%m-%d')=date_format(:time,'%Y-%m-%d'),date_format(update_time,'%Y-%m')=date_format(:time,'%Y-%m')),date_format(update_time,'%Y')=date_format(:time,'%Y')) " +
            " and user_id in (select id from user_info where platform=:platform)",nativeQuery = true)
    int selectUserLogCountByTimeAndPlatform(@Param("flag") Integer flag,@Param("time") String time,@Param("platform") String platform);


    @Query(value = "select log.update_time as updateTime,u.nick_name as userName,u.id as userId from user_log log " +
            "left join user_info u on u.id=log.user_id   " +
            "where if(:flag!=1,if(:flag!=2,date_format(log.update_time,'%Y-%m-%d')=date_format(:time,'%Y-%m-%d'),date_format(log.update_time,'%Y-%m')=date_format(:time,'%Y-%m')),date_format(log.update_time,'%Y')=date_format(:time,'%Y')) ",
            countQuery = "select count(*) from user_log log " +
                    "left join user_info u on u.id=log.id   " +
                    "where if(:flag!=1,if(:flag!=2,date_format(log.update_time,'%Y-%m-%d')=date_format(:time,'%Y-%m-%d'),date_format(log.update_time,'%Y-%m')=date_format(:time,'%Y-%m')),date_format(log.update_time,'%Y')=date_format(:time,'%Y')) ",
            nativeQuery = true)
    Page<Map<String,Object>> selectTeamUserLogList(Pageable pageable, @Param("flag") Integer flag, @Param("time") String time);






}
